package com.wxb.generator.sql.utils;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * describe:
 *
 * @author : wxbing
 * @date : 2021/11/22
 */
public class SqlGeneratorUtil {

	/**
	 * main
	 *
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		//读取excel文件的路径
		String readurl = "C:\\Users\\wxbing\\Desktop\\source.xlsx";
		readExcel(readurl);
	}

	/**
	 * @param filePath
	 * @return
	 * @throws Exception
	 */
	public static List<Map> readExcel(String filePath) throws Exception {
		File file = new File(filePath);
		if (!file.exists()) {
			return null;
		}

		// Get the suffix of the file
		String suffix = filePath.substring(filePath.lastIndexOf("."));

		// if suffix cannot equals .xls && .xlsx ,return null
		if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
			return null;
		}

		//返回值列
		List<Map> reaultList = new ArrayList<Map>();
		if (".xls".equals(suffix) || ".xlsx".equals(suffix)) {
			reaultList = readExcel2007(filePath);
		}
		return reaultList;
	}

	/**
	 * @param filePath
	 * @return
	 * @throws IOException
	 */
	public static List<Map> readExcel2007(String filePath) throws IOException {
		List<Map> valueList = new ArrayList<Map>();
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(filePath);
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(fis);

			int sheetNum = xwb.getNumberOfSheets();
			/**
			 *  foreach to read sheet.
			 */
			XSSFSheet sheet;
			XSSFRow row;

			for (int s = 0; s <= sheetNum - 1; s++) {
				sheet = xwb.getSheetAt(s);
				String table_name = sheet.getSheetName();
				int totalRow = sheet.getLastRowNum();
				StringBuilder sqlBuilder = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
				sqlBuilder.append(table_name).append("(");
				for (int index = 1; index <= totalRow; index++) {
					row = sheet.getRow(index);

					// 字段名
					sqlBuilder.append("`").append(row.getCell(0)).append("` ");

					// 字段类型
					sqlBuilder.append(row.getCell(1));

					// 约束
					XSSFCell restrict = row.getCell(2);
					if (restrict != null && "1".equals(forceGetStringValue(restrict))) {
						sqlBuilder.append(" NOT NULL ");
					}
					// 是否自动递增
					XSSFCell autoIncrement = row.getCell(4);
					if (autoIncrement != null && "1".equals(forceGetStringValue(autoIncrement))) {
						sqlBuilder.append(" AUTO_INCREMENT ");
					}

					// 注释
					XSSFCell comment = row.getCell(5);
					if (comment != null) {
						sqlBuilder.append(" COMMENT ");
						sqlBuilder.append("'").append(forceGetStringValue(comment)).append("'");
					}

					if (index == totalRow) {
						sqlBuilder.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
					} else {
						sqlBuilder.append(", ");
					}

					// 主键   PRIMARY KEY (`pk_id`),
					XSSFCell primary = row.getCell(3);
					if (primary != null && "1".equals(forceGetStringValue(primary))) {
						sqlBuilder.append(" PRIMARY KEY (`").append(row.getCell(0)).append("`), ");
					}

				}
				System.out.println(sqlBuilder);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			fis.close();
		}
		return valueList;
	}

	/**
	 * 强制获取String类型
	 * int CELL_TYPE_NUMERIC = 0;
	 * int CELL_TYPE_STRING = 1;
	 * int CELL_TYPE_FORMULA = 2;
	 * int CELL_TYPE_BLANK = 3;
	 * int CELL_TYPE_BOOLEAN = 4;
	 * int CELL_TYPE_ERROR = 5;
	 */
	private static String forceGetStringValue(XSSFCell cell) {
		int cellType = cell.getCellType();
		String value = "";
		if (Cell.CELL_TYPE_NUMERIC == cellType) {
			double doubleVal = cell.getNumericCellValue();
			return String.valueOf((int) doubleVal);
		} else if (Cell.CELL_TYPE_STRING == cellType) {
			return cell.getStringCellValue();
		}
		return value;
	}

}
